package pos.server.dac.dcs;

import com.google.gwt.user.server.rpc.RemoteServiceServlet;
import pos.domain.Corso;
import pos.domain.Piano;
import pos.domain.Utente;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Collection;
import java.util.HashSet;
import java.util.logging.Level;
import java.util.logging.Logger;
import pos.client.dac.dcs.CorsoDCS;
import pos.server.dac.ConnectionManager;
import pos.server.dac.exceptions.EccezioneCRUD;

/**
 *
 * @author Alessio
 */
public class CorsoDCSImpl extends RemoteServiceServlet implements CorsoDCS {

    private static final String FIND_BY_UTENTE_SQL = "SELECT * FROM corso WHERE Utente = ?";

    @Override
    public Collection<Corso> findByUtente(Utente u) throws EccezioneCRUD {
        Collection<Corso> res = new HashSet<Corso>();
        try {
            Connection con = null;
            PreparedStatement pstmt = null;
            con = ConnectionManager.getConnection();
            pstmt = con.prepareStatement(FIND_BY_UTENTE_SQL);
            pstmt.setString(1, u.getLogin());
            ResultSet rs = pstmt.executeQuery();
            while (rs.next()) {
                res.add(new Corso(rs.getString("nome"), rs.getInt("anno"), rs.getInt("semestre"), rs.getInt("crediti"), rs.getString("docente")));
            }
            pstmt.close();
            con.close(); //si assume la modalita’ autocommit
        } catch (ClassNotFoundException ex) {
            throw new RuntimeException(ex.getCause());
        } catch (SQLException ex) {
            throw new EccezioneCRUD(ex.getMessage());
        }
        return res;
    }
    private static final String FIND_BY_PIANO_SQL = "SELECT * FROM composto_da, corso WHERE Piano = ? AND corso.Utente = composto_da.corso_utente AND corso_nome = nome";

    @Override
    public Collection<Corso> findByPiano(Piano p) throws EccezioneCRUD {
        Connection con = null;
        PreparedStatement pstmt = null;
        Collection<Corso> res = new HashSet<Corso>();
        try {

            con = ConnectionManager.getConnection();
            pstmt = con.prepareStatement(FIND_BY_PIANO_SQL);
            pstmt.setInt(1, p.getId_piano());
            ResultSet rs = pstmt.executeQuery();
            while (rs.next()) {
                res.add(new Corso(rs.getString("nome"), rs.getInt("anno"), rs.getInt("semestre"), rs.getInt("crediti"), rs.getString("docente")));
            }
        } catch (ClassNotFoundException ex) {
            throw new RuntimeException(ex.getCause());
        } catch (SQLException ex) {
            throw new EccezioneCRUD(ex.getMessage());
        } finally {
            try {
                if (pstmt != null) {
                    pstmt.close();
                }
                if (con != null) {
                    con.close();
                }
            } catch (SQLException ex) {
                Logger.getLogger(UniversitaServiceImpl.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
        return res;
    }
}
